<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Victor
 * Date: 06.12.11
 * Time: 2:55
 * To change this template use File | Settings | File Templates.
 */

class Category_Manager extends Core_BaseModel
{
    public function getCountCoursesInCategory()
    {
        $pdo = $this->dataModule->getConnection();
        $sql = "select `ct`.`idcategory`,count(`cr`.`idcourse`)as `total courses`   from `dzundza_course` as `cr`
left outer join `dzundza_category` as `ct`  on `cr`.`category_idcategory` = `ct`.`idcategory`
group by `ct`.`idcategory` ";

        $stn = $pdo->prepare($sql);
        $stn->execute();
        $result = $stn->fetchAll(PDO::FETCH_KEY_PAIR);
        return $result;

    }

    public function getAllCategories()
    {
        try {
            $pdo = $this->dataModule->getConnection();
            $sql = "SELECT `idCategory`,`categoryName` FROM `dzundza_category`";
            $stn = $pdo->query($sql);
            $stn->execute();
            $result = $stn->fetchAll(2);
            $countsCourses = $this->getCountCoursesInCategory();
            foreach ($result as $key => $value) {
                if (isset($countsCourses[$value['idCategory']])) {
                    $countCoursesinCurrentCategory = $countsCourses[$value['idCategory']];
                } else

                    $countCoursesinCurrentCategory = 0;
                $array[$value['idCategory']] = array($value['categoryName'], $countCoursesinCurrentCategory);
            }

            return $array;
        } catch (PDOException $e) {
            echo "<pre>";
            var_dump($e);
            echo "</pre>";
        }
    }

    public function getCategoryName($id)
    {
        $pdo = $this->dataModule->getConnection();
        $sql = "SELECT `categoryName` from `dzundza_category` where `idcategory` = :id";
        $stn = $pdo->prepare($sql);
        $stn->bindParam(':id', $id, PDO::PARAM_INT);

        $stn->execute();
        $result = $stn->fetch();
        return $result;
    }

    /**
     * @throws Exception
     * @param Category $category
     * @return idCategory|int
     */
    public function addCategory(Category $category)
    {
        $pdo = null;
        $stn = null;

        if ($category instanceof Category) {
            //echo $category->getCategoryName()."<br/>";
            //echo $category->getRaiting()."<br/>";
            try {
                $pdo = $this->dataModule->getConnection();
                $sql = "INSERT INTO `dzundza_category`
            (`raiting`,
            `categoryName`)
            VALUES
            (:raiting,
            :categoryName)";

                $stn = $pdo->prepare($sql);
                $stn->bindParam(':raiting', $category->getRaiting(), PDO::PARAM_INT);
                $stn->bindParam(':categoryName', $category->getCategoryName(), PDO::PARAM_STR);
                $pdo->beginTransaction();
                $stn->execute();
                $pdo->commit();
                $result = $stn->fetch(PDO::FETCH_ASSOC);
                return $result['idcategory'];

            } catch (PDOException $e)
            {
                $pdo->rollBack();
                echo "PDOException: " . $e->getMessage() . "<br/>";

            } catch(Exception $e){
                header("Location:http://www.google.com.ua");
            }

        } else
        {
            throw new Exception('Parameter `category` has incorrect type' . gettype($category) . '<br/>');

        }
    }

    public function removeCategory($id)
    {
        try {
            $pdo = $this->dataModule->getConnection();
            $sql = "DELETE FROM `dzundza_category`
WHERE `idcategory` = :id";

            $stn = $pdo->prepare($sql);
            $stn->bindParam(':id', $id, PDO::PARAM_INT);

            $pdo->beginTransaction();
            $stn->execute();
            $pdo->commit();


        } catch (PDOException $e)
        {
            $pdo->rollBack();
            echo "PDOException: " . $e->getMessage() . "<br/>";

        }
    }


}
